﻿Imports System
Imports System.Data
Imports GestionHorarios
Public Class cConsultas
    Public Shared Function Obtener_periodos(ByRef dtbPeriodos As DataTable) As Boolean
        Dim bolResultado As Boolean = True
        Try
            Dim cComando As New SqlClient.SqlCommand("SELECT CODIGO_PERIODO,DESCRIPCION_PERIODO FROM PERIODOS")
            dtbPeriodos = New DataTable()
            If Not cMotorBD.EjecutarConsulta(cComando, dtbPeriodos) Then
                Throw New Exception("Error al obtener los periodos.")
            End If
        Catch ex As Exception
            bolResultado = False
        End Try
        Return bolResultado
    End Function
    Public Shared Function Obtener_Turnos(ByRef dtbTurnos As DataTable) As Boolean
        Dim bolResultado As Boolean = True
        Try
            Dim cComando As New SqlClient.SqlCommand("SELECT CODIGO_TURNO,DESCRIPCION_TURNO FROM TURNOS")
            dtbTurnos = New DataTable()
            If Not cMotorBD.EjecutarConsulta(cComando, dtbTurnos) Then
                Throw New Exception("Error al obtener los Turnos.")
            End If
        Catch ex As Exception
            bolResultado = False
        End Try
        Return bolResultado
    End Function

    Public Shared Function Obtener_Asignaturas(ByRef dtbAsignaturas As DataTable) As Boolean
        Dim bolResultado As Boolean = True
        Try
            Dim cComando As New SqlClient.SqlCommand("SELECT CODIGO_ASIGNATURA,NOMBRE FROM ASIGNATURAS")
            dtbAsignaturas = New DataTable()
            If Not cMotorBD.EjecutarConsulta(cComando, dtbAsignaturas) Then
                Throw New Exception("Error al obtener los Asignaturas.")
            End If
            Dim r As DataRow = dtbAsignaturas.NewRow
            r(0) = "0"
            r(1) = "Seleccione una opción"
            dtbAsignaturas.Rows.InsertAt(r, 0)
        Catch ex As Exception
            bolResultado = False
        End Try
        Return bolResultado
    End Function
    Public Shared Function Obtener_Horario(ByVal strfiltro As String, ByVal parametros As SqlClient.SqlParameter(), ByRef dtbDatos As DataTable) As Boolean
        Dim bolresultado As Boolean = True
        Try

        Catch ex As Exception
            bolresultado = False
        End Try
        Return bolresultado

    End Function

    Public Shared Function Obtener_Franjas(ByRef dtbDatos As DataTable, ByVal parametros As SqlClient.SqlParameter()) As Boolean
        Dim bolresultado As Boolean = True
        Try
            Dim cmd As New SqlClient.SqlCommand("select codigo_franja_horaria,curso_academico,descripcion_franja from franjas_horarias where CODIGO_TIPO_CLASE='TE' and curso_academico=@curso_academico and codigo_turno=@codigo_turno")
            For Each param As SqlClient.SqlParameter In parametros
                cmd.Parameters.Add(param)
            Next
            dtbDatos = New DataTable()
            If Not (cMotorBD.EjecutarConsulta(cmd, dtbDatos)) Then
                Throw New Exception("Error al obtener las franjas horarias")
            End If
            dtbDatos.Columns.Add(New DataColumn("Lunes"))
            dtbDatos.Columns.Add(New DataColumn("Martes"))
            dtbDatos.Columns.Add(New DataColumn("Miercoles"))
            dtbDatos.Columns.Add(New DataColumn("Jueves"))
            dtbDatos.Columns.Add(New DataColumn("Viernes"))
            dtbDatos.Columns.Add(New DataColumn("Sabado"))

        Catch ex As Exception
            bolresultado = False
        End Try
        Return bolresultado

    End Function
    Public Shared Function Obtener_Versiones(ByRef dtbDatos As DataTable, ByVal parametros As SqlClient.SqlParameter()) As Boolean
        Dim bolresultado As Boolean = True
        Try
            Dim cmd As New SqlClient.SqlCommand("SELECT NUMERO_VERSION, CODIGO_HORARIO FROM HORARIOS " & _
                                         "WHERE CURSO_ACADEMICO=@CURSO_ACADEMICO " & _
                                            "AND FECHA_LUNES_SEMANA = @FECHA " & _
                                            "AND CODIGO_PERIODO=@CODIGO_PERIODO " & _
                                            "AND CODIGO_TIPO_HORARIO=@CODIGO_TIPO" & _
                                         " ORDER BY NUMERO_VERSION DESC")
            For Each param As SqlClient.SqlParameter In parametros
                cmd.Parameters.Add(param)
            Next
            dtbDatos = New DataTable()
            If Not (cMotorBD.EjecutarConsulta(cmd, dtbDatos)) Then
                Throw New Exception("Error al obtener las franjas horarias")
            End If

        Catch ex As Exception
            bolresultado = False
        End Try
        Return bolresultado

    End Function
    Public Shared Function Crear_Nuevo_Horario(ByVal parametros As SqlClient.SqlParameter()) As Boolean
        
        Dim bolresultado As Boolean = True
        Try
            Dim cmd As New SqlClient.SqlCommand("INSERT INTO HORARIOS (CODIGO_HORARIO,FECHA_LUNES_SEMANA,CODIGO_GRUPO,NUMERO_VERSION,CODIGO_TIPO_CLASE,CURSO_ACADEMICO,CODIGO_PERIODO,CODIGO_TIPO_HORARIO) VALUES(@HORARIO,@FECHA,@GRUPO,@VERSION,@CLASE,@CURSO,@PERIODO,@TIPO_HORARIO)")
            For Each param As SqlClient.SqlParameter In parametros
                cmd.Parameters.Add(param)
            Next

            If Not (cMotorBD.Ejecutar(cmd)) Then
                Throw New Exception("Error al obtener las franjas horarias")
            End If

        Catch ex As Exception
            bolresultado = False
        End Try
        Return bolresultado
    End Function
    Public Shared Function Borrar_Registro_Clase(ByVal parametros As SqlClient.SqlParameter()) As Integer
        Dim intRetorno As Integer = 0
        Dim cmd As New SqlClient.SqlCommand("DELETE FROM CLASES WHERE CODIGO_HORARIO=@HORARIO")
        For Each param As SqlClient.SqlParameter In parametros
            cmd.Parameters.Add(param)
        Next
        intRetorno = cMotorBD.EjecutarFilas(cmd)
        Return intRetorno
    End Function
    Public Shared Function Obtener_Plan(ByVal stringAsignatura As String) As String
        Dim strRetorno As String
        Dim dtbDatos As New DataTable
        Dim cmd As New SqlClient.SqlCommand("SELECT CODIGO_PLAN FROM ASIGNATURAS WHERE CODIGO_ASIGNATURA=@ASIGNATURA")
        cmd.Parameters.AddWithValue("ASIGNATURA", stringAsignatura)
        cMotorBD.EjecutarConsulta(cmd, dtbDatos)
        strRetorno = Convert.ToString(dtbDatos.Rows(0)(0))
        Return strRetorno
    End Function
    Public Shared Function Crear_Registro_Clase(ByVal parametros As SqlClient.SqlParameter()) As Integer
        Dim intRetorno As Integer = 0
        'insert into clases(codigo_horario,codigo_dia_semana,curso_academico,codigo_franja_horaria,codigo_tipo_clase,codigo_comienzo_clase,codigo_grupo, id_usuario,codigo_plan,codigo_asignatura)
        ' values(@horario,@dia,@curso,@franja,'TU','','EGT25',1,@plan,@asignatura)
        Dim cmd As New SqlClient.SqlCommand("insert into clases(codigo_horario,codigo_dia_semana,curso_academico,codigo_franja_horaria,codigo_tipo_clase,codigo_comienzo_clase,codigo_grupo, id_usuario,codigo_plan,codigo_asignatura) " & _
        " values(@horario,@dia,@curso,@franja,'TU','','EGT25','admin',@plan,@asignatura)")
        For Each param As SqlClient.SqlParameter In parametros
            cmd.Parameters.Add(param)
        Next
        intRetorno = cMotorBD.EjecutarFilas(cmd)
        Return intRetorno
    End Function
    Public Shared Function DiaSemana(ByVal iDia As Integer) As String
        Dim lDias As New List(Of String)
        lDias.AddRange(New String() {"L", "M", "X", "J", "V", "S", "D"})
        Return lDias.Item(iDia)
    End Function
    Public Shared Function ObtenerCodigoAsignatura(ByVal strHorario As String, ByVal dia As String, ByVal franja As String) As String
        Dim strAsignatura As String
        Dim cmd As New SqlClient.SqlCommand("SELECT CODIGO_ASIGNATURA FROM CLASES WHERE DIA=@DIA AND CODIGO_HORARIO=@horario and codigo_franja_horaria=@franja")
        cmd.Parameters.AddWithValue("dia", dia)
        cmd.Parameters.AddWithValue("horario", strHorario)
        cmd.Parameters.AddWithValue("franja", Convert.ToInt32(franja))
        Dim dtbDatos As New DataTable
        cMotorBD.EjecutarConsulta(cmd, dtbDatos)
        If dtbDatos.Rows.Count = 0 Then Return "0"
        strAsignatura = Convert.ToString(dtbDatos.Rows(0)(0))
        Return strAsignatura

    End Function
End Class
